<?php
	PutEnv("ORACLE_HOME=/usr/local/dbpackages/oracle");

	$c = oci_connect('comp231stu116', '47600210', 'comp231.cse.ust.hk');

	if(!$c) {
		echo "<p>Unable to connect:" . var_dump(oci_error($c));
		die();
	}
//--------------Query -----------------------------
//--------------flight ----------------------------
	if($_POST['flight']) {
		$count = 0;
		$s = 'select * from flight ';
		$actri = array('AC_type', 'airline_code', 'flight_ID', 'STD', 'STA', 'team_ID', 'gate_ID');
		$d = array(1, 1, 0, 1, 1, 0, 0);
		
		for($i = 0; $i < 7; $i++) {
		
			if($_POST['f'.$i] && $_POST['fv' .$i]) {
				if($count == 0)
					$s = $s. "where ";
				if($count > 0)
					$s = $s. "and ";
				$count++;
				if ($d[$i] == 1)
					$s = $s. $actri[$i]. "='" .$_POST['fv'.$i]. "' ";
				else
					$s = $s. $actri[$i]. "=" .$_POST['fv'.$i]. " ";
			}
		}

		echo $s."<br>";
		
		if($count > 0) {
			//------ select * from flight where ..... 
			$q = oci_parse($c,$s);
			oci_execute($q);
			echo $q;
			
			echo $S. "<br>";
			while ($row = oci_fetch_array($q, OCI_BOTH)) {
				echo "<table border='1'>";
				echo "<tr><td>Airline</td><td>" .$row[0]. "</td></tr>";
				echo "<tr><td>Flight number</td><td>" .$row[1]. "</td></tr>";
				echo "<tr><td>Flight type</td><td>" .$row[2]. "</td></tr>";
				echo "<tr><td>STA</td><td>" .$row[3]. "</td></tr>";
				echo "<tr><td>STD</td><td>" .$row[4]. "</td></tr>";
				//echo "<tr><td>LCT</td><td>" .$row[5]. "</td></tr>";
				echo "<tr><td>Aircraft type</td><td>" .$row[6]. "</td></tr>";
				echo "<tr><td>Service team</td><td>" .$row[7]. "</td></tr>";
				echo "<tr><td>Gate</td><td>" .$row[8]. "</td></tr>";
				echo "</table>";
			}
		}
		
	}
//-------------------staff ---------------------
	if($_POST['staff']) {
		$count = 0;
		$s = "select * from staff ";
		$actri = array('ID', 'surname', 'given_name', 'working_time_slot', 'skill_list');
		$d = array(0, 1, 1, 1, 0);
		
		for($i = 0; $i < 5; $i++) {
			if($_POST['s'.$i] && $_POST['sv'.$i]) {
				if($count == 0)
					$s = $s. "where ";
				if($count > 0)
					$s = $s. "and ";
				$count++;
				if ($d[$i] == 1)
					$s = $s. $actri[$i]. "='" .$_POST['sv'.$i]. "' ";
				else
					$s = $s. $actri[$i]. "=" .$_POST['sv'.$i]. " ";
			}
		}

		echo $s."<br>";
		
		if($count > 0) {
			//-------- select * from staff where .....
			$q = oci_parse($c,$s);
			oci_execute($q);
			
			while ($row = oci_fetch_array($q, OCI_BOTH)) {
			
			$dl = "select driver_license_number from driver where driver_ID =" .$row[0];
			$dl_q = oci_parse($c,$dl);
			oci_execute($dl_q);
			
			//--------- find the driver license -----------------
			if (oci_fetch($dl_q))
				$rr = OCIResult($dl_q, "DRIVER_LICENSE_NUMBER");
			else
				$rr = "Nil";
			//---------------------------------
				
				echo "<table border='1'>";
				echo "<tr><td>Staff ID</td><td>" .$row[0]. "</td></tr>";
				echo "<tr><td>Surname</td><td>" .$row[1]. "</td></tr>";
				echo "<tr><td>Given name</td><td>" .$row[2]. "</td></tr>";
				echo "<tr><td>Working status</td><td>" .$row[3]. "</td></tr>";
				echo "<tr><td>Skill list</td><td>" .$row[4]. "</td></tr>";
				echo "<tr><td>Reward per hour</td><td>" .$row[5]. "</td></tr>";
				echo "<tr><td>Driver license</td><td>" .$rr. "</td></tr>";
				echo "</table>";
				
			}
		}
		
	}

//------------------ team -----------------------------
	if($_POST['team']) {
	
	/*
	$s1 = "insert into team values (100)";
	$s2 = "insert into form_1_team values (00009, 100)";
	$s3 = "insert into form_2_team values (00019, 100)";
	
	$q1 = oci_parse($c, $s1);
	oci_execute($q1);

	$q2 = oci_parse($c, $s2);
	oci_execute($q2);

	$q3 = oci_parse($c, $s3);
	oci_execute($q3);
	*/
		
		$count = 0;
		$s = "select distinct team_ID from (select * from form_1_team join staff on (form_1_team.staff_ID=staff.ID) union 
							 select * from form_2_team join staff on (form_2_team.driver_ID=staff.ID)) ";
							//form_1_team join staff on (form_1_team.staff_ID=staff.ID), 
						    //form_2_team join staff on (form_2_team.driver_ID=staff.ID) ";
		$actri = array('team_ID','skill_list', 'working_time_slot');
		$d = array(0,0,1);
		
		for($i = 0; $i < 3; $i++) {
			if($_POST['t'.$i] && $_POST['tv'.$i]) {
				if($count == 0)
					$s = $s. "where ";
				if($count > 0)
					$s = $s. "and ";
				$count++;
				if ($d[$i] == 1)
					$s = $s. $actri[$i]. "='" .$_POST['tv'.$i]. "' ";
				else
					$s = $s. $actri[$i]. "=" .$_POST['tv'.$i]. " ";
			}
		}
		
		//$s = $s." and form_2_team.team_ID = form_1_team.team_ID ";
		
		echo $s."<br>";
		
		if($count > 0) {
			//wrong ------------------------
			$q = oci_parse($c,$s);
			oci_execute($q);
			
		
		
			while ($row = oci_fetch_array($q, OCI_BOTH)) {
			// not done -----------------------
				
				
				$s2 = "select * from form_1_team join staff on (form_1_team.staff_ID=staff.ID), 
					  			     form_2_team join staff on (form_2_team.driver_ID=staff.ID) 
									 where form_1_team.team_id=form_2_team.team_id and 
										   form_1_team.team_id=".$row[0];
				$q2 = oci_parse($c,$s2);
				oci_execute($q2);
				while ($row2 = oci_fetch_array($q2, OCI_BOTH)) {
					echo "<table border='1'>";
					echo "<tr><td>Team ID</td><td>" .$row2[1]. "</td></tr>";
					echo "<tr><td>Team Members</td><td>" .$row2[3]." ".$row2[4]."<br>".$row2[12]." ".$row2[13]. "</td></tr>";
					echo "<tr><td>Team Working time slot</td><td>" .$row2[6]. "</td></tr>";
					echo "<tr><td>Team skill list</td><td>" .$row2[7]."<br>".$row2[16]. "</td></tr>";
					echo "<tr><td>Team reward per hour:</td><td>" .(floatval($row2[8])+floatval($row2[17]))."</td></tr>";
					echo "</table><br>";
				}
			
				
			}
		}
		
	}
	
	
?>
